<?php
/**
 * 执行会员系统数据库迁移
 * 在根目录下运行: php database/migrate_membership.php
 */

// 数据库配置
$dbConfig = [
    'host' => '101.35.252.112',
    'port' => '45508',
    'database' => 'trace',
    'username' => 'root',
    'password' => 'CeyZFHxjPABhp2Hz',
    'charset' => 'utf8mb4',
    'prefix' => 'gf_'
];

try {
    // 创建PDO连接
    $dsn = "mysql:host={$dbConfig['host']};port={$dbConfig['port']};dbname={$dbConfig['database']};charset={$dbConfig['charset']}";
    $pdo = new PDO($dsn, $dbConfig['username'], $dbConfig['password'], [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
    ]);
    
    echo "数据库连接成功\n";
    
    // 会员套餐配置表
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS gf_membership_plans (
          id BIGINT PRIMARY KEY AUTO_INCREMENT,
          plan_type VARCHAR(50) NOT NULL COMMENT '套餐类型: basic, premium, enterprise',
          name VARCHAR(100) NOT NULL COMMENT '套餐名称',
          description TEXT COMMENT '套餐描述',
          price DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '价格（元/月）',
          app_limit INT NOT NULL DEFAULT 1 COMMENT '应用数量限制',
          features JSON COMMENT '功能特性配置',
          is_active TINYINT(1) DEFAULT 1 COMMENT '是否启用',
          sort_order INT DEFAULT 0 COMMENT '排序',
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          UNIQUE KEY uniq_plan_type (plan_type)
        )
    ");
    echo "✅ 会员套餐表创建成功\n";
    
    // 插入默认套餐配置
    $pdo->exec("
        INSERT INTO gf_membership_plans (plan_type, name, description, price, app_limit, features, sort_order) VALUES
        ('basic', '基础版', '适合个人开发者，免费使用，限制1个应用', 0.00, 1, '{\"support\": \"community\", \"analytics\": \"basic\", \"storage\": \"1GB\"}', 1),
        ('premium', '高级版', '适合小型团队，无应用数量限制', 99.00, -1, '{\"support\": \"email\", \"analytics\": \"advanced\", \"storage\": \"10GB\", \"custom_domain\": true}', 2),
        ('enterprise', '企业版', '适合大型企业，提供专属服务', 999.00, -1, '{\"support\": \"dedicated\", \"analytics\": \"enterprise\", \"storage\": \"unlimited\", \"custom_domain\": true, \"api_rate_limit\": \"high\"}', 3)
    ");
    echo "✅ 默认会员套餐已插入\n";
    
    // 用户会员信息表
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS gf_user_memberships (
          id BIGINT PRIMARY KEY AUTO_INCREMENT,
          user_id BIGINT NOT NULL COMMENT '用户ID',
          plan_type VARCHAR(50) NOT NULL COMMENT '套餐类型',
          status VARCHAR(20) DEFAULT 'active' COMMENT '状态: active, expired, cancelled',
          start_date DATE NOT NULL COMMENT '开始日期',
          end_date DATE NOT NULL COMMENT '结束日期',
          auto_renew TINYINT(1) DEFAULT 1 COMMENT '是否自动续费',
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          INDEX idx_user_id (user_id),
          INDEX idx_status_end_date (status, end_date),
          FOREIGN KEY (plan_type) REFERENCES gf_membership_plans(plan_type)
        )
    ");
    echo "✅ 用户会员表创建成功\n";
    
    // 支付参数配置表
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS gf_payment_config (
          id BIGINT PRIMARY KEY AUTO_INCREMENT,
          provider VARCHAR(50) NOT NULL DEFAULT 'yipay' COMMENT '支付提供商',
          config_key VARCHAR(100) NOT NULL COMMENT '配置键',
          config_value TEXT COMMENT '配置值',
          is_active TINYINT(1) DEFAULT 1 COMMENT '是否启用',
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          UNIQUE KEY uniq_provider_key (provider, config_key)
        )
    ");
    echo "✅ 支付配置表创建成功\n";
    
    // 插入易支付默认配置
    $pdo->exec("
        INSERT INTO gf_payment_config (provider, config_key, config_value, is_active) VALUES
        ('yipay', 'api_url', 'https://pay.gggua.com/', 1),
        ('yipay', 'merchant_id', '1295', 1),
        ('yipay', 'merchant_key', 'pD7J5Cz5755oFp5o0AaAhzaAnp45En4O', 1),
        ('yipay', 'notify_url', '/api/payment/notify', 1),
        ('yipay', 'return_url', '/payment/success', 1)
    ");
    echo "✅ 易支付配置已插入\n";
    
    // 用户余额表
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS gf_user_balances (
          id BIGINT PRIMARY KEY AUTO_INCREMENT,
          user_id BIGINT NOT NULL COMMENT '用户ID',
          balance DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '余额（元）',
          frozen_balance DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '冻结余额',
          total_recharge DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '总充值金额',
          total_consume DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '总消费金额',
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          UNIQUE KEY uniq_user_id (user_id)
        )
    ");
    echo "✅ 用户余额表创建成功\n";
    
    // 账单记录表
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS gf_billing_records (
          id BIGINT PRIMARY KEY AUTO_INCREMENT,
          user_id BIGINT NOT NULL COMMENT '用户ID',
          bill_type VARCHAR(50) NOT NULL COMMENT '账单类型: recharge, consumption, refund',
          bill_subtype VARCHAR(50) COMMENT '子类型: membership, balance, service',
          amount DECIMAL(10,2) NOT NULL COMMENT '金额',
          balance_before DECIMAL(10,2) NOT NULL COMMENT '操作前余额',
          balance_after DECIMAL(10,2) NOT NULL COMMENT '操作后余额',
          related_id BIGINT COMMENT '关联ID（如会员ID、订单ID）',
          description TEXT COMMENT '描述',
          payment_method VARCHAR(50) COMMENT '支付方式',
          transaction_id VARCHAR(100) COMMENT '第三方交易ID',
          status VARCHAR(20) DEFAULT 'completed' COMMENT '状态: pending, completed, failed, refunded',
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          INDEX idx_user_id (user_id),
          INDEX idx_bill_type (bill_type),
          INDEX idx_created_at (created_at),
          INDEX idx_transaction_id (transaction_id)
        )
    ");
    echo "✅ 账单记录表创建成功\n";
    
    // 会员订单表
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS gf_membership_orders (
          id BIGINT PRIMARY KEY AUTO_INCREMENT,
          user_id BIGINT NOT NULL COMMENT '用户ID',
          plan_type VARCHAR(50) NOT NULL COMMENT '套餐类型',
          order_no VARCHAR(64) NOT NULL COMMENT '订单号',
          amount DECIMAL(10,2) NOT NULL COMMENT '订单金额',
          discount_amount DECIMAL(10,2) DEFAULT 0.00 COMMENT '折扣金额',
          final_amount DECIMAL(10,2) NOT NULL COMMENT '实际支付金额',
          months INT NOT NULL DEFAULT 1 COMMENT '购买月数',
          start_date DATE COMMENT '会员开始日期',
          end_date DATE COMMENT '会员结束日期',
          payment_method VARCHAR(50) COMMENT '支付方式',
          payment_status VARCHAR(20) DEFAULT 'pending' COMMENT '支付状态',
          transaction_id VARCHAR(100) COMMENT '第三方交易ID',
          auto_renew TINYINT(1) DEFAULT 1 COMMENT '是否自动续费',
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          UNIQUE KEY uniq_order_no (order_no),
          INDEX idx_user_id (user_id),
          INDEX idx_payment_status (payment_status),
          INDEX idx_created_at (created_at)
        )
    ");
    echo "✅ 会员订单表创建成功\n";
    
    // 自动扣费记录表
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS gf_auto_charge_records (
          id BIGINT PRIMARY KEY AUTO_INCREMENT,
          user_id BIGINT NOT NULL COMMENT '用户ID',
          membership_id BIGINT NOT NULL COMMENT '会员ID',
          amount DECIMAL(10,2) NOT NULL COMMENT '扣费金额',
          discount_rate DECIMAL(5,2) DEFAULT 100.00 COMMENT '折扣率（百分比）',
          final_amount DECIMAL(10,2) NOT NULL COMMENT '实际扣费金额',
          charge_type VARCHAR(50) DEFAULT 'membership_renew' COMMENT '扣费类型',
          status VARCHAR(20) DEFAULT 'success' COMMENT '状态: success, failed, pending',
          error_message TEXT COMMENT '错误信息',
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          INDEX idx_user_id (user_id),
          INDEX idx_membership_id (membership_id),
          INDEX idx_created_at (created_at)
        )
    ");
    echo "✅ 自动扣费记录表创建成功\n";
    
    // 系统配置表
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS gf_system_config (
          id BIGINT PRIMARY KEY AUTO_INCREMENT,
          config_key VARCHAR(100) NOT NULL COMMENT '配置键',
          config_value TEXT COMMENT '配置值',
          config_type VARCHAR(50) DEFAULT 'string' COMMENT '配置类型: string, int, float, json, bool',
          description TEXT COMMENT '配置描述',
          is_system TINYINT(1) DEFAULT 0 COMMENT '是否系统配置',
          is_active TINYINT(1) DEFAULT 1 COMMENT '是否启用',
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          UNIQUE KEY uniq_config_key (config_key)
        )
    ");
    echo "✅ 系统配置表创建成功\n";
    
    // 插入系统默认配置
    $pdo->exec("
        INSERT INTO gf_system_config (config_key, config_value, config_type, description, is_system) VALUES
        ('balance_renew_discount', '90', 'int', '余额自动续费折扣（百分比）', 1),
        ('auto_renew_before_days', '7', 'int', '会员到期前多少天开始自动续费', 1),
        ('max_failed_charge_attempts', '3', 'int', '自动扣费最大失败尝试次数', 1),
        ('min_balance_for_renew', '50.00', 'float', '自动续费最低余额要求', 1),
        ('enable_auto_renew', '1', 'bool', '是否启用自动续费功能', 1),
        ('enable_balance_payment', '1', 'bool', '是否启用余额支付', 1)
    ");
    echo "✅ 系统默认配置已插入\n";
    
    // 用户操作日志表
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS gf_user_logs (
          id BIGINT PRIMARY KEY AUTO_INCREMENT,
          user_id BIGINT NOT NULL COMMENT '用户ID',
          action VARCHAR(100) NOT NULL COMMENT '操作类型',
          resource_type VARCHAR(50) COMMENT '资源类型',
          resource_id BIGINT COMMENT '资源ID',
          old_data JSON COMMENT '旧数据',
          new_data JSON COMMENT '新数据',
          ip_address VARCHAR(45) COMMENT 'IP地址',
          user_agent TEXT COMMENT '用户代理',
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          INDEX idx_user_id (user_id),
          INDEX idx_action (action),
          INDEX idx_created_at (created_at),
          INDEX idx_resource (resource_type, resource_id)
        )
    ");
    echo "✅ 用户操作日志表创建成功\n";
    
    // 创建索引优化查询
    $pdo->exec("CREATE INDEX idx_user_membership_active ON gf_user_memberships(user_id, status, end_date)");
    $pdo->exec("CREATE INDEX idx_billing_date ON gf_billing_records(user_id, created_at)");
    $pdo->exec("CREATE INDEX idx_balance_updated ON gf_user_balances(user_id, updated_at)");
    echo "✅ 索引创建成功\n";
    
} catch (PDOException $e) {
    echo "❌ 数据库执行失败: " . $e->getMessage() . "\n";
    exit(1);
} catch (Exception $e) {
    echo "❌ 执行失败: " . $e->getMessage() . "\n";
    exit(1);
}

echo "\n🎉 数据库迁移完成！\n";
echo "📊 已创建以下数据表:\n";
echo "   - gf_membership_plans (会员套餐表)\n";
echo "   - gf_user_memberships (用户会员表)\n";
echo "   - gf_payment_config (支付配置表)\n";
echo "   - gf_user_balances (用户余额表)\n";
echo "   - gf_billing_records (账单记录表)\n";
echo "   - gf_membership_orders (会员订单表)\n";
echo "   - gf_auto_charge_records (自动扣费记录表)\n";
echo "   - gf_system_config (系统配置表)\n";
echo "   - gf_user_logs (用户操作日志表)\n";